using System;
using System.Data.OleDb;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

/// <summary>
/// Summary description for database
/// </summary>
/// 
namespace database
{
    public class database
    {
    string _connstring;
    	public database()
    	{
            _connstring = null;
        }
        public MySqlConnection connection()
            {
                _connstring = ConfigurationSettings.AppSettings["InvConnectionString"];
                MySqlConnection _dbo = new MySqlConnection(_connstring);
                return(_dbo);
            }
        public DataSet CustomDataSet(string table, string[] fields)
        {
            int i = 0;
            
            DataSet ds = new DataSet();
            ds.Tables.Add(table);
            foreach (string columns in fields)
            {
                ds.Tables[table].Columns.Add(columns,System.Type.GetType("System.String"));
                i++;
            }
            return ds;    
        }
        public void UpdateSingleRow(string table, string indexfield, string index, string[] parameters, string[] values, MySqlConnection _dbo)
        {
            int i;
            System.Text.StringBuilder fullquery = new System.Text.StringBuilder(); 
            MySqlCommand cmd = new MySqlCommand();

            i = 0;
            fullquery.Append ("update " + table + " set ");
            for(i=0; i<parameters.Length; i++)
            {
                fullquery.Append (parameters[i] + " = " + values[i] + ", ");
            }
            fullquery.Remove(fullquery.Length - 2, 2);
            fullquery.Append (" where " + indexfield + " = " + index + ";");
            
            _dbo.Open();
            cmd.Connection = _dbo;
            cmd.CommandText = fullquery.ToString();
            cmd.Prepare();
            cmd.ExecuteNonQuery();
            _dbo.Close();
        }
        
        public DataSet AddRowCustomDataSet(DataSet ds, string table, string[] parameters, string[] values)
        {
            int i = new int();
            i = 0;
            DataRow dtRow = ds.Tables[table].NewRow();

            foreach (string param in parameters)
            {
                dtRow[param] = values[i];
                i++;
            }
            ds.Tables[table].Rows.Add(dtRow);
            return ds;

        }
        public void DeleteDataRows(string table, string where, MySqlConnection conn)
        {
            System.Text.StringBuilder fullquery = new System.Text.StringBuilder();
            MySqlCommand cmd = new MySqlCommand();

            fullquery.Append("delete from " + table + " where " + where);
            conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = fullquery.ToString();
            cmd.Prepare();
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ee)
            {

                //MessageBox.Show(ee.ToString());
            }
            finally
            {
                conn.Close();
            }
        }
        public string SingleSelect(string select, string table, string where, MySqlConnection conn)
        {
            System.Text.StringBuilder fullquery = new System.Text.StringBuilder();
            DataSet dataset = new DataSet();
            fullquery.Append("select ");
            fullquery.Append(select);
            fullquery.Append(" from ");
            fullquery.Append(table);
            fullquery.Append(" where ");
            fullquery.Append(where);
            conn.Open();
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            adapter.SelectCommand = new MySqlCommand(fullquery.ToString(), conn);

            adapter.Fill(dataset);
            conn.Close();
            return dataset.Tables[0].Rows[0].ItemArray[0].ToString();
        }
        
        public DataSet SingleSelect(MySqlConnection conn, string select, string table, string where)
        {
            System.Text.StringBuilder fullquery = new System.Text.StringBuilder();
            DataSet dataset = new DataSet();
            fullquery.Append("select ");
            fullquery.Append(select);
            fullquery.Append(" from ");
            fullquery.Append(table);
            fullquery.Append(" where ");
            fullquery.Append(where);
            conn.Open();
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            adapter.SelectCommand = new MySqlCommand(fullquery.ToString(), conn);
            
            adapter.Fill(dataset);
            conn.Close();
            return dataset;
        }
        public DataSet SelectRows(MySqlConnection conn, string query)
            {
                DataSet dataset = new DataSet();
                conn.Open();
                MySqlDataAdapter adapter = new MySqlDataAdapter();
                adapter.SelectCommand = new MySqlCommand(query, conn);
                adapter.Fill(dataset);
                conn.Close();
                return dataset;
            }
        public DataSet SelectRows(MySqlConnection conn, MySqlDataAdapter da)
        {
            DataSet dataset = new DataSet();
            conn.Open();
            da.Fill(dataset);
            conn.Close();
            return dataset;
        }

        public void PerformSP(MySqlConnection conn, string procedure, string[] parameters, string[] values)
        {
            MySqlCommand cmd = new MySqlCommand();
            int i;

            i = 0;
            try
            {
                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = procedure;
                cmd.CommandType = CommandType.StoredProcedure;

                foreach (string param in parameters)
                {
                    if (param.Substring(0, 1) == "n")
                    {
                        cmd.Parameters.Add(param.Substring(1, (param.Length - 1)), Convert.ToInt16(values[i]));
                    }
                    else
                    {
                        cmd.Parameters.Add(param.Substring(1, (param.Length - 1)), values[i].ToString());
                    }
                    cmd.Parameters[param.Substring(1, (param.Length - 1))].Direction = ParameterDirection.Input;
                    i++;
                }

                cmd.ExecuteNonQuery();

            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                //MessageBox.Show("Error " + ex.Number + " has occurred: ");
            }
            finally
            {
                conn.Close();
            }

        }

        public bool verifyQuery(string runQuery)
        {
            database db = new database();
            DataSet ds = new DataSet();

            try
            {
                db.SelectRows(db.connection(), runQuery);
            }
            catch
            {
                return false;
            }
            return true;
        }

        public void InsertRows(MySqlConnection conn, string table, string[] parameters, string[] values)
        {
            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            DataSet dataset = new DataSet();
            MySqlCommand cmd = new MySqlCommand();
            int i = new int();
            string nulltest = "test";
            
            conn.Open();
            cmd.Connection = conn;

            sb.Append("insert into ");
            sb.Append(table);
            sb.Append(" (");
            foreach (string param in parameters)
            {
                sb.Append(param.Substring(1,(param.Length-1)));
                sb.Append(", ");
            }
            sb.Remove(sb.Length - 2, 2);
            sb.Append(") VALUES (");

            for (i = 0; i <= (parameters.Length - 1); i++)
            {
                nulltest = values[i] ?? "null";
                if ((parameters[i].Substring(0, 1) == "n") || (nulltest == "null"))
                {
                    if (nulltest == "null")
                    {
                        sb.Append("null");
                    }
                    else
                    {
                        sb.Append(values[i]);
                    }
                    sb.Append(", ");
                }
                else
                {
                    sb.Append("'");
                    sb.Append(values[i]);
                    sb.Append("', ");
                }
            }
            sb.Remove(sb.Length - 2, 2);
            sb.Append(")");
                    
            cmd.CommandText = sb.ToString();
            cmd.Prepare();
//            try
//            {
                cmd.ExecuteNonQuery();
//            }
//            catch (Exception ee)
//            {
//               
//                MessageBox.Show(ee.ToString());
//            }
//            finally
//            {
                conn.Close();
//            }
        }
    }
}
public class DataSetHelper
{
    public DataSet ds;
    
    public DataSetHelper(ref DataSet DataSet)
    {
        ds = DataSet;
    }
    
    public DataSetHelper()
    {
        ds = null;
    }
    
    private bool ColumnEqual(object A, object B)
    {

        // Compares two values to see if they are equal. Also compares DBNULL.Value.
        // Note: If your DataTable contains object fields, then you must extend this
        // function to handle them in a meaningful way if you intend to group on them.

        if (A == DBNull.Value && B == DBNull.Value) //  both are DBNull.Value
            return true;
        if (A == DBNull.Value || B == DBNull.Value) //  only one is DBNull.Value
            return false;
        return (A.Equals(B));  // value type standard comparison
    }
    
    public DataTable SelectDistinct(string TableName, DataTable SourceTable, string FieldName)
    {
        DataTable dt = new DataTable(TableName);
        dt.Columns.Add(FieldName, SourceTable.Columns[FieldName].DataType);

        object LastValue = null;
        foreach (DataRow dr in SourceTable.Select("", FieldName))
        {
            if (LastValue == null || !(ColumnEqual(LastValue, dr[FieldName])))
            {
                LastValue = dr[FieldName];
                dt.Rows.Add(new object[] { LastValue });
            }
        }
        if (ds != null)
            ds.Tables.Add(dt);
        return dt;
    }		
}
